using System;
using System.Collections.Generic;
using System.Data;
using System.Text;

namespace QLHXNK.DataAccess
{
    class MonthlyExpensesData
    {
        #region---------------------------------------------Thêm, xoá, sửa-----------------------------------------

        public static int CreateMonthlyExpenses(int monthName, int yearName, int expensesID, double amount, String remark)
        {
            try
            {
                string sqlstring = "insert into Monthly_Expenses(month_name,year_name,expenses_id,amount,remark) values(" + monthName + "," + yearName + ","
                                    + expensesID + "," + amount + ", '" + remark.Replace("'", "''") + "')";
                ConnectionData connectionData = new ConnectionData();
                if (connectionData.Command(sqlstring) != 0)
                    return Int32.Parse(GetLastMonthlyExpenses().Rows[0]["ID"].ToString());
                return 0;
            }
            catch
            {
                return 0;
            }
        }


        public static int DeleteMonthlyExpenses(int monthlyExpensesID, int monthName, int yearName)
        {
            try
            {
                string queryCondition = "";
                if (monthlyExpensesID != -1)
                    queryCondition += " ID=" + monthlyExpensesID + " ";
                if(monthName!=-1 && yearName !=-1)
                    queryCondition += " month_name=" + monthName + " and year_name="+yearName+" ";
                string sqlstring = "delete from Monthly_Expenses ";
                if (queryCondition != "")
                    sqlstring += " where " + queryCondition;
                ConnectionData connectionData = new ConnectionData();
                return connectionData.Command(sqlstring);
            }
            catch
            {
                return 0;
            }
        }


        public static int UpdateMonthlyExpenses(int monthlyExpensesID, int expensesID, double amount, String remark)
        {
            try
            {
                string sqlstring = "update Monthly_Expenses set  expenses_id = " + expensesID + ",amount=" + amount
                                    + ", remark='" + remark.Replace("'", "''") + "' where ID=" + monthlyExpensesID + "";
                ConnectionData connectionData = new ConnectionData();
                return connectionData.Command(sqlstring);
            }
            catch
            {
                return 0;
            }
        }

        #endregion

        #region ------------------------------------------Lấy thông tin item---------------------------------------

        

        public static DataTable ListMonthlyExpenses()
        {
            try
            {
                string sqlstring = "select month_name, year_name ,sum(amount) as tongtien from Monthly_Expenses group by month_name,year_name order by year_name";
                ConnectionData connectionData = new ConnectionData();
                return connectionData.List(sqlstring);
            }
            catch
            {
                return null;
            }
        }

        public static DataTable GetLastMonthlyExpenses()
        {
            try
            {
                string sqlstring = "select * from Monthly_Expenses order by ID desc ";
                ConnectionData connectionData = new ConnectionData();
                return connectionData.List(sqlstring);
            }
            catch
            {
                return null;
            }
        }

        public static DataTable GetMonthlyExpensesByMonth(int monthName, int yearName, String expexnsesCode)
        {
            try
            {
                string sqlstring = "select e.ID, e.expenses_code, e.expenses_name, me.month_name,me.year_name, me.amount, me.remark,me.ID from Expenses e, Monthly_Expenses me where e.ID= me.expenses_id and me.month_name=" + monthName + " and year_name="+yearName+"";
                if (expexnsesCode != "")
                    sqlstring += " and e.expenses_code='" + expexnsesCode + "' ";
                ConnectionData connectionData = new ConnectionData();
                return connectionData.List(sqlstring);
            }
            catch
            {
                return null;
            }
        }

        
        #endregion
    }
}
